﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Runtime.InteropServices;
using MySql.Data;
using MySql.Data.MySqlClient;
using System.Data;
using System.Data.SqlClient;

namespace DBQueryTools
{
    public abstract class SQLConnection:IDisposable
    {
        public abstract DataTable GetDataTable(string sqlcmd);
        void IDisposable.Dispose()
        {

        }

    }

    public class MySQLDBConnetion : SQLConnection ,IDisposable
    {
        [DllImport("Setting.dll", CharSet = CharSet.Ansi)]
        static extern string GetSetting(string strTitle);

        private MySqlConnection connection = null;

        public MySQLDBConnetion(bool mode, string serverip, string catalog)
        {
            string[] dbips = serverip.Split(new char[] { ':' });
            string sPort = (dbips.Length > 1 ? dbips[1] : "3306");
            serverip = dbips[0];

            if (mode)
                Connetion(GetSetting("MYSQL_ADM_Account"), GetSetting("MYSQL_ADM_Pasword"), serverip, catalog, sPort);
            else
                Connetion(GetSetting("MYSQL_QUERY_Account"), GetSetting("MYSQL_QUERY_Pasword"), serverip, catalog, sPort);
        }

        public MySQLDBConnetion(bool mode, string serverip, string catalog, string sPort)
        {
            string[] dbips = serverip.Split(new char[] { ':' });
            string port = (dbips.Length > 1 ? dbips[1] : "3306");
            serverip = dbips[0];
            port = (sPort == "" ? port : sPort);

            if (mode)
                Connetion(GetSetting("MYSQL_ADM_Account"), GetSetting("MYSQL_ADM_Pasword"), serverip, catalog, port);
            else
                Connetion(GetSetting("MYSQL_QUERY_Account"), GetSetting("MYSQL_QUERY_Pasword"), serverip, catalog, port);
        }

        public MySQLDBConnetion(string serverip, string account, string password, string catalog)
        {
            string[] dbips = serverip.Split(new char[] { ':' });
            string sPort = (dbips.Length > 1 ? dbips[1] : "3306");
            serverip = dbips[0];

            Connetion(account, password, serverip, catalog, sPort);
        }

        public MySQLDBConnetion(string serverip, string account, string password, string catalog, string sPort)
        {
            string[] dbips = serverip.Split(new char[] { ':' });
            string port = (dbips.Length > 1 ? dbips[1] : "3306");
            serverip = dbips[0];
            port = (sPort == "" ? port : sPort);

            Connetion(account, password, serverip, catalog, port);
        }

        public void Dispose()
        {
            connection.Close();
            connection.Dispose();
        }

        public void Connetion(string user, string password, string serverip, string dbname, string port)
        {
            if (connection != null)
                connection.Close();

            string connStr = "Server=" + serverip + ";Database=" + dbname + ";Port =" + port + ";Uid= " + user + ";" + "Pwd=" + password + ";Character Set=utf8";
            ;
            connection = new MySqlConnection(connStr);
            connection.Open();
        }

        public int NonQueryExec(string sqlcmd)
        {
            if (connection == null)
                return 0;

            MySqlCommand setcode = new MySqlCommand(sqlcmd, connection);
            setcode.CommandType = CommandType.Text;
            int result = setcode.ExecuteNonQuery();
            return result;
        }

        public override DataTable GetDataTable(string sqlcmd)
        {
            if (connection == null)
                return null;

            MySqlCommand setcode = new MySqlCommand(sqlcmd, connection);
            setcode.CommandType = CommandType.Text;

            MySqlDataAdapter da = new MySqlDataAdapter(setcode);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];
        }
    }

    public class MSSQLDBConnetion : SQLConnection, IDisposable
    {
        [DllImport("Setting.dll", CharSet = CharSet.Ansi)]
        static extern string GetSetting(string strTitle);

        private int timeOut = 1200;
        private SqlConnection sqlConnection = null;

        public MSSQLDBConnetion(bool mode, string serverip, string catalog)
        {
            string[] dbips = serverip.Split(new char[] { ':' });
            string sPort = (dbips.Length > 1 ? dbips[1] : "1433");
            serverip = dbips[0];
            if (mode)
                Connetion(GetSetting("MSSQL_ADM_Account"), GetSetting("MSSQL_ADM_Pasword"), serverip, catalog, sPort);
            else
                Connetion(GetSetting("MSSQL_QUERY_Account"), GetSetting("MSSQL_QUERY_Pasword"), serverip, catalog, sPort);
        }

        public MSSQLDBConnetion(bool mode, string serverip, string catalog, string sPort)
        {
            string[] dbips = serverip.Split(new char[] { ':' });
            string port = (dbips.Length > 1 ? dbips[1] : "1433");
            serverip = dbips[0];
            port = (sPort == "" ? port : sPort);
            if (mode)
                Connetion(GetSetting("MSSQL_ADM_Account"), GetSetting("MSSQL_ADM_Pasword"), serverip, catalog, port);
            else
                Connetion(GetSetting("MSSQL_QUERY_Account"), GetSetting("MSSQL_QUERY_Pasword"), serverip, catalog, port);
        }

        public MSSQLDBConnetion(string user, string password, string serverip, string catalog)
        {
            string[] dbips = serverip.Split(new char[] { ':' });
            string sPort = (dbips.Length > 1 ? dbips[1] : "1433");
            serverip = dbips[0];

            Connetion(user, password, serverip, catalog, sPort);
        }

        public MSSQLDBConnetion(string user, string password, string serverip, string catalog, string sPort)
        {
            string[] dbips = serverip.Split(new char[] { ':' });
            string port = (dbips.Length > 1 ? dbips[1] : "1433");
            serverip = dbips[0];
            port = (sPort == "" ? port : sPort);

            Connetion(user, password, serverip, catalog, port);
        }

        public void Dispose()
        {
            sqlConnection.Close();
            sqlConnection.Dispose();
        }

        public void Connetion(string user, string password, string serverip, string catalog)
        {
            string strConnetionStr = "user id=" + user + ";data source=" + serverip + ";persist security info=True;initial catalog=" + catalog + ";password=" + password;
            sqlConnection = new SqlConnection(strConnetionStr);
            sqlConnection.Open();
        }

        public void Connetion(string user, string password, string serverip, string catalog, string port)
        {
            string strConnetionStr = "user id=" + user + ";data source=" + serverip + "," + port + ";persist security info=True;initial catalog=" + catalog + ";password=" + password;
            sqlConnection = new SqlConnection(strConnetionStr);
            sqlConnection.Open();
        }

        public void CloseConnetion()
        {
            sqlConnection.Close();
        }

        // 取指定数据库的当前时间
        public string GetSysTime()
        {
            SqlCommand sql = new SqlCommand("SELECT GETDATE() AS Expr1 FROM G_PaoQian_Data");
            sql.CommandTimeout = timeOut;
            DataTable dt = new DataTable();

            SqlDataAdapter Sql = new SqlDataAdapter(sql);
            Sql.SelectCommand.Connection = sqlConnection;
            if (Sql.SelectCommand.Connection == null)
                return null;

            if (Sql.SelectCommand.Connection.State == ConnectionState.Closed)
                Sql.SelectCommand.Connection.Open();
            Sql.Fill(dt);
            if (dt.Rows.Count == 0)
                throw new Exception("GetDate出现错误<<-O->>取数据库时间错误");

            return dt.Rows[0][0].ToString();
        }

        public override DataTable GetDataTable(string sqlcmd)
        {
            SqlCommand sql = new SqlCommand(sqlcmd, sqlConnection);
            sql.CommandTimeout = timeOut;
            SqlDataAdapter Sql = new SqlDataAdapter(sql);

            sql.ExecuteNonQuery();

            DataTable dataTable = new DataTable();
            Sql.Fill(dataTable);

            return dataTable;
        }

        public int ExecuteNonQuery(string sqlcmd)
        {
            SqlCommand sql = new SqlCommand(sqlcmd, sqlConnection);
            sql.CommandTimeout = timeOut;
            SqlDataAdapter Sql = new SqlDataAdapter(sql);

            return sql.ExecuteNonQuery();
        }

        /*****************************************
        public DataTable ExecProcessEx( SqlCommand sql, string Server )
        {
            DataTable dt = new DataTable();
            SqlDataAdapter Sql = new SqlDataAdapter( sql );
            Sql.SelectCommand.Connection = sqlConnection;

            if ( Sql.SelectCommand.Connection.State == ConnectionState.Closed )
                Sql.SelectCommand.Connection.Open();
            Sql.Fill( dt );
            return dt;
        }

        public void ExecProcess( SqlCommand sql, string Server )
        {
            sql.Connection = sqlConnection;
            if ( sql.Connection.State == ConnectionState.Closed )
                sql.Connection.Open();
            sql.ExecuteNonQuery();
        }

        public int ExecSql( string strSQL )
        {
            // 操作的时候关联到连接对象
            SqlCommand sql = new SqlCommand( strSQL );
            sql.CommandTimeout = timeOut;
            sql.Connection = sqlConnection;

            if ( sql.Connection.State == ConnectionState.Closed )
                sql.Connection.Open();
            return sql.ExecuteNonQuery();
        }
        ******************************************/
    };
}
